# -*- coding: UTF-8 -*-
import pymssql
import sys


reload(sys)   
sys.setdefaultencoding('utf8')

conn=pymssql.connect(host='.',database='scm_main')
cur=conn.cursor()

def chosetype():
    print u'请选择消费软件类型：1、联机版；2、实时版。c、跳过版本选择'
    name=raw_input()
    #print (type(name))
    if name=='1':
        print u'联机版'
        print u'创建用户视图：'
        cur.execute(u"""
        if OBJECT_ID('view_nt_zhye') is not null
            drop view view_nt_zhye
        """)

        cur.execute(u"""
                CREATE view [dbo].[view_nt_zhye] as
                    select c.* ,
                    (select a.ac_money from dt_ac_user a where a.user_serial=c.user_serial) as Syje,
                    case when c.user_dep in(200001,200081,200085,200078,200080,200082) then 1
                    else 2 end as sfid
                    from dt_user c
                """)
        print u'用户视图创建成功'

        print u'消费记录视图创建：'
        cur.execute(u"""
           if OBJECT_ID('view_nt_xfmx') is not null
                drop view view_nt_xfmx
        
                """)
        cur.execute(u"""
           create view view_nt_xfmx
                as
                select u.user_serial,u.user_lname,u.user_card,u.user_no,
                                a.acdep_serial as '场所编号',
                                d.dep_name as '场所名称',
                                a.sj  as '交易时间',
                                CONVERT(VARCHAR(10),a.sj,120)  as '交易日期',
                                CONVERT(VARCHAR(10),a.sj,108)  as  time,
                                a.xh ,
                                case when a.lx in(12,1,51,52) then 1
                                                when a.lx in(2,4,34,35) then 2
                                                when a.lx in (8) then 18 end as xz,
                                ISNULL(a.Dev_serial,0) as '设备编号',
                                b.mc as '设备名称',
                                a.Dev_time_no as '餐别编号',
                                c.lname as '餐别名称',
                                a.Dep_serial as '部门编号',
                                e.dep_name as '部门名称',
                                (a.old_money) as '交易前金额',
                                (a.new_money+a.new_sub+a.new_add) as '消费明细',
                                (a.save_money) as '交易后金额',
                                a.jl_sj as '记录上传时间'
                from xf_mx a left join st_device b on a.Dev_serial=b.bh
                                           left join xf_time c on a.Dev_time_no=c.bh
                                           left join dt_ac_dep d on a.acdep_serial=d.dep_serial
                                           left join dt_dep e on a.Dep_serial=e.dep_serial
                                           left join dt_user u on a.User_serial=u.User_serial
                where     a.lx in(12,2,4,34,35,1,51,52,8)        
                """)
        print u'消费记录视图创建成功'
        print u'考勤记录视图创建：'
        cur.execute(u"""
          if OBJECT_ID('view_kt_jl') is not null
                DROP VIEW view_kt_jl
                """)
        cur.execute(u"""
          CREATE VIEW view_kt_jl
AS
SELECT     a.bh, a.user_serial, b.user_no AS 工号, b.user_lname AS 姓名, b.user_depname AS 部门名称, CONVERT(varchar, a.sj, 120) AS 刷卡时间, a.dev_serial AS 设备编号, c.mc AS 设备名称, 
                      c.dev_sb_id AS 设备ID
FROM         dbo.kt_jl AS a INNER JOIN
                      dbo.dt_user AS b ON a.user_serial = b.user_serial INNER JOIN
                      dbo.st_device AS c ON a.dev_serial = c.bh;
                """)
        print u'考勤记录试图创建成功'

        cur.execute(u"""
                if OBJECT_ID('ty_kq') is not null
                    DROP table ty_kq;
                """)
        cur.execute(u'CREATE TABLE ty_kq([id] [int] NULL)')

        cur.execute(u"""
                if OBJECT_ID('ty_xf') is not null
                    DROP table ty_xf
                """)
        cur.execute(u'CREATE TABLE ty_xf(	[id] [int] NULL)')
             
        cur.execute(u"""
                IF  OBJECT_ID('ty_lock') is not null
                    DROP TABLE ty_lock
                """)
        cur.execute(u'CREATE TABLE ty_lock([id] [int] NULL)')
        cur.execute(u'INSERT INTO ty_lock ([id]) VALUES (1)')

        print u'记录表创建成功'


        
    elif name=='2':
        print u'实时版'
        cur.execute(u"""
        if OBJECT_ID('view_nt_zhye') is not null
            drop view view_nt_zhye
        """)

        cur.execute(u"""
                CREATE view [dbo].[view_nt_zhye] as
                    select c.* ,
                    (select a.ac_money from dt_ac_user a where a.user_serial=c.user_serial) as Syje,
                    case when c.user_dep in(200001,200081,200085,200078,200080,200082) then 1
                    else 2 end as sfid
                    from dt_user c
                """)
        print u'用户视图创建成功'

        print u'消费记录视图创建：'
        cur.execute(u"""
           if OBJECT_ID('view_nt_xfmx') is not null
                drop view view_nt_xfmx
        
                """)
        cur.execute(u"""
           create view view_nt_xfmx
as
select u.user_serial,u.user_lname,u.user_card,u.user_no,
		a.acdep_serial as '场所编号',
		d.dep_name as '场所名称',
		a.Deal_sj  as '交易时间',
		CONVERT(VARCHAR(10),a.Deal_sj,120)  as '交易日期',
		CONVERT(VARCHAR(10),a.Deal_sj,108)  as  time,
		a.xh ,
		case when a.deal_lx in(1,31) then 1
				when a.deal_lx in(121,131,141,34,35) then 2
				when a.deal_lx in (8) then 18 end as xz,
		ISNULL(a.Dev_serial,0) as '设备编号',
		b.mc as '设备名称',
		a.Dev_time_no as '餐别编号',
		c.lname as '餐别名称',
		a.Dep_serial as '部门编号',
		e.dep_name as '部门名称',
		(a.Old_card_cash+a.Old_card_subsidy) as '交易前金额',
		(a.Cash_amt+a.Sub_amt) as '消费明细',
		--(a.New_card_cash+a.New_card_subsidy) as '交易后金额',
		(a.New_cash+a.New_subsidy) as '交易后金额',
		a.WAcc_sj as '记录上传时间'
from view_mx_all a left join st_device b on a.Dev_serial=b.bh
			   left join xf_time c on a.Dev_time_no=c.bh
			   left join dt_ac_dep d on a.acdep_serial=d.dep_serial
			   left join dt_dep e on a.Dep_serial=e.dep_serial
			   left join dt_user u on a.User_serial=u.User_serial
where     a.deal_lx in(1,31,8,121,131,141,34,35)  
                """)
        print u'消费记录视图创建成功'
        print u'考勤记录视图创建：'
        cur.execute(u"""
          if OBJECT_ID('view_kt_jl') is not null
                DROP VIEW view_kt_jl
                """)
        cur.execute(u"""
          CREATE VIEW view_kt_jl
AS
SELECT     a.bh, a.user_serial, b.user_no AS 工号, b.user_lname AS 姓名, b.user_depname AS 部门名称, CONVERT(varchar, a.sj, 120) AS 刷卡时间, a.dev_serial AS 设备编号, c.mc AS 设备名称, 
                      c.dev_sb_id AS 设备ID
FROM         dbo.kt_jl AS a INNER JOIN
                      dbo.dt_user AS b ON a.user_serial = b.user_serial INNER JOIN
                      dbo.st_device AS c ON a.dev_serial = c.bh;
                """)
        print u'考勤记录试图创建成功'

        cur.execute(u"""
                if OBJECT_ID('ty_kq') is not null
                    DROP table ty_kq;
                """)
        cur.execute(u'CREATE TABLE ty_kq([id] [int] NULL)')
        cur.execute(u"""
                if OBJECT_ID('ty_mj') is not null
                    DROP table ty_kq;
                """)
        cur.execute(u'CREATE TABLE ty_mj([id] [int] NULL)')

        cur.execute(u"""
                if OBJECT_ID('ty_xf') is not null
                    DROP table ty_xf
                """)
        cur.execute(u'CREATE TABLE ty_xf(	[id] [int] NULL)')
             
        cur.execute(u"""
                IF  OBJECT_ID('ty_lock') is not null
                    DROP TABLE ty_lock
                """)
        cur.execute(u'CREATE TABLE ty_lock(	[id] [int] NULL)')
        cur.execute(u'INSERT INTO ty_lock ([id]) VALUES (1)')

        print u'记录表创建成功'
    elif name=='c':
		print u'跳过版本版本选择'
    else :
        print u'选择错误，请重新选择！'
        chosetype()

def inputDW():
    print u'请输入单位编号：'
    name=raw_input()
    with open(sys.path[0]+'/dwid', 'w') as f:
        f.write(name)
  
inputDW()
chosetype()

conn.commit()
cur.close()
conn.close()

  
